using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using Newtonsoft.Json;
using T.FAS.Runtime.Base.Entity;
using T.FAS.Runtime.Metadata.Entity.DataModel;
using T.FAS.Runtime.DataAccess.Common;
using T.FAS.Runtime.DataAccess.SQLDom;

namespace T.FAS.Runtime.DataAccess.Engine
{
    public class QueryPlanFilterHelper
    {
        #region Private Props & Fields
        private const string UserDefinedColumn = "__UserDefineColumns";
        /// <summary>
        /// 特殊操作符，需要针对不同数据库做处理
        /// </summary>
        private static HashSet<string> s_specialOpe = new HashSet<string>
        {
            QueryOperator.Contains,
            QueryOperator.NotContains,
            QueryOperator.StartsWith,
            QueryOperator.EndsWith,
            QueryOperator.Between,
        };
        /// <summary>
        /// 高级查询中所有的参数变量替换集合
        /// </summary>
        private readonly Dictionary<string, FilterParam> _params = new Dictionary<string, FilterParam>();
        /// <summary>
        /// Group结构化的查询条件
        /// </summary>
        private readonly Group _group;
        /// <summary>
        /// 数据类型
        /// </summary>
        private readonly DatabaseType _databaseType;
        /// <summary>
        /// 已生成SQL Paramters Code的集合，确保Code生成不重复
        /// </summary>
        /// <summary>
        /// 生成IDbDataParameter集合。
        /// </summary>
        private readonly DataQuery _dataQuery;

        #endregion

        public QueryPlanFilterHelper(string groupJson, DataQuery dataQuery)
        {
            if (!string.IsNullOrEmpty(groupJson))
            {
                try
                {
                    _group = JsonConvert.DeserializeObject<Group>(groupJson);
                    _dataQuery = dataQuery;
                    string dbType = _dataQuery.DataQuerySources.Find(s => s.IsMainSource).DataObject.Database.DbType;
                    _databaseType = (DatabaseType)Enum.Parse(typeof(DatabaseType), dbType);
                }
                catch (Exception e)
                {
                    throw new FasException(DataAccessErrorCode.DataQuery.QueryPlanError, string.Format(CommonStringClassEngine.QueryPlanFilterHelper_, groupJson, dataQuery?.ID), e);
                }
            }
        }

        public AdvanceQuerySQL GetAdvanceQuerySQL()
        {
            if (_group == null)
                return null;
            var result = new AdvanceQuerySQL
            {
                SQL = QueryGroupToSQL(_group),
                DBParams = DataPrameterTransfer(),
                ParamList = _params,
                AdvancedFilterExtSetting = _group.ExtSetting,
            };
            ValidateAndConvertParamListValue();
            return result;
        }

        #region Private Funcs
        /// <summary>
        /// Query Group转SQL
        /// </summary>
        /// <param name="group">分组</param>
        /// <param name="depth">深度</param>
        /// <param name="offset">偏移量</param>
        /// <returns></returns>
        private string QueryGroupToSQL(Group group, int depth = 0, int offset = 0)
        {
            if (group == null)
                return null;
            StringBuilder sb = new StringBuilder();
            if (group.Filters != null && group.Filters.Count > 0)
            {
                int groupCount = 0;
                for (int i = 0; i < group.Filters.Count; i++)
                {
                    var item = group.Filters[i];
                    if (item is Filter filter)
                    {
                        sb.Append($" {FilterToSQL(filter, depth, offset, i)} ");
                    }
                    else if (item is FRelation relation)
                    {
                        sb.Append($" {relation.Relation} ");
                    }
                    else if (item is Group childGroup)
                    {
                        sb.Append($" ({QueryGroupToSQL(childGroup, depth + 1, groupCount++)}) ");
                    }
                }
            }
            return sb.ToString();
        }

        /// <summary>
        /// 将Filter对象转换为SQL
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        private string FilterToSQL(Filter filter, int groupDepth, int groupOffset, int filterOffset)
        {
            if (filter.ValueType == AdvancedQueryValueType.Value)
                return ValueFilterToSQL(filter, groupDepth, groupOffset, filterOffset);
            else
                return ColumnFilterToSQL(filter, groupDepth, groupOffset, filterOffset);
        }

        /// <summary>
        /// 列条件生成SQL
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        private string ColumnFilterToSQL(Filter filter, int groupDepth, int groupOffset, int filterOffset)
        {
            string valueCode = filter.Value.FirstOrDefault();
            FilterParser parser = GetFilterParser(_databaseType);
            parser.Filter = filter;
            //尝试根据高级查询的filter code参数反查出对应的DQ列
            GetDataQueryColumnByFilter(filter, _dataQuery, out DataQueryColumn leftColumn, out DataQueryColumn rightColumn);
            //特殊处理国际化列
            if (leftColumn != null && leftColumn.DataColumn != null && leftColumn.DataColumn.IsInternational)
            {
                Dictionary<string, string> mapping = InternationalTools.GetLanguageMapping(InternationalMode.CurrentUser);
                if (mapping.Count == 1)
                {
                    var dataObject = _dataQuery.DataQuerySources.FirstOrDefault(s => s.ID == leftColumn.DataQuerySourceID)?.DataObject;
                    if (dataObject != null)
                    {
                        var realColumnName = InternationalTools.GetRealFieldName(dataObject, leftColumn.DataColumn.ColumnName, mapping.First().Key);
                        var prefix = parser.Filter.Code.Split('.')[0];
                        parser.Filter.Code = prefix + "." + realColumnName;
                    }
                }
            }
            if (rightColumn != null && rightColumn.DataColumn != null && rightColumn.DataColumn.IsInternational)
            {
                Dictionary<string, string> mapping = InternationalTools.GetLanguageMapping(InternationalMode.CurrentUser);
                if (mapping.Count == 1)
                {
                    var dataObject = _dataQuery.DataQuerySources.FirstOrDefault(s => s.ID == rightColumn.DataQuerySourceID)?.DataObject;
                    if (dataObject != null)
                    {
                        var realColumnName = InternationalTools.GetRealFieldName(dataObject, rightColumn.DataColumn.ColumnName, mapping.First().Key);
                        var prefix = parser.Filter.Value?.FirstOrDefault()?.Split('.')[0];
                        if (prefix != null)
                            parser.Filter.Code = prefix + "." + realColumnName;
                    }
                }
            }
            //如果为用户自定义列，则特殊处理下比较的sql脚本内容
            if (IsUserDefinedColumnFilter(filter, _dataQuery, out DataQueryColumn leftUserDefinedColumn, out DataQueryColumn rightUserDefinedColumn))
            {
                if (leftUserDefinedColumn != null)
                    filter.Code = $"({leftUserDefinedColumn.ColumnExpression})";
                if (rightUserDefinedColumn != null)
                    valueCode = $"({rightUserDefinedColumn.ColumnExpression})";
            }
            parser.ValueCode = valueCode;
            return parser.ToSQL();
        }

        private string ValueFilterToSQL(Filter filter, int groupDepth, int groupOffset, int filterOffset)
        {
            //生成变量替换符用的Code。例如@Name 的Name。同时防止被自定义列替换Code所影响。
            string paramCode = GenerateParameterCode(filter, groupDepth, groupOffset, filterOffset);
            FilterParser parser = GetFilterParser(_databaseType);
            parser.Filter = filter;
            DatabaseColumnType databaseColumnType;
            if (EnvironmentSetting.MulitLanguage.IsEnableUnicode)
                databaseColumnType = DatabaseColumnType.Nvarchar;
            else
                databaseColumnType = DatabaseColumnType.Varchar;

            DataType dataType = DataType.String;
            bool isNullable = true;
            //如果使用了自定义列作为查询参数。
            if (IsUserDefinedColumnFilter(filter, _dataQuery, out DataQueryColumn leftColumn, out DataQueryColumn rightColumn))
            {
                //如果左值为自定义列，替换Code内容。
                if (leftColumn != null)
                {
                    filter.Code = $"({leftColumn.ColumnExpression})";

                }
                //如果右值为自定义列，直接返回SQL
                if (rightColumn != null)
                {
                    parser.ValueCode = $"({rightColumn.ColumnExpression})";
                    return parser.ToSQL();
                }
                if (leftColumn != null)
                {
                    try
                    {
                        if (!string.IsNullOrEmpty(leftColumn.ColumnDataType))
                        {
                            dataType = DataType.String;
                            if (DataTypeUtil.TryConvertToDataType(leftColumn.ColumnDataType, out dataType))
                            {
                                databaseColumnType = DataTypeUtil.ConvertDatabaseColumnTypeFromDataType(dataType);
                            }
                        }
                    }
                    catch
                    {
                    }
                }
            }
            else
            {
                GetDataQueryColumnByFilter(filter, _dataQuery, out leftColumn, out rightColumn);
                //普通列使用DO的列的数据库字段类型
                if (!string.IsNullOrEmpty(leftColumn?.DataColumn?.DataType))
                {
                    //条件使用条件查询对应的DQ->DO列的数据库字段类型。
                    DatabaseColumnType databaseColumnDataType;
                    string dataTypeStr = leftColumn.DataColumn.DataType;
                    string appDataTypeStr = leftColumn.DataColumn.ApplicationDataType;
                    //如果为外键类型，使用外键DO的主键列的数据类型
                    if (leftColumn.DataColumn.IsFkColumn && leftColumn.DataColumn.RelatedDataObjectPrimaryKey != null)
                    {
                        dataTypeStr = leftColumn.DataColumn.RelatedDataObjectPrimaryKey.DataType;
                        appDataTypeStr = leftColumn.DataColumn.RelatedDataObjectPrimaryKey.ApplicationDataType;
                    }
                    //尝试获取低代码数据类型枚举值
                    DataTypeUtil.TryConvertToDataType(appDataTypeStr, out dataType);
                    //给DatabaseColumnType类型赋初始值：默认为Varchar
                    if (EnvironmentSetting.MulitLanguage.IsEnableUnicode)
                        databaseColumnDataType = DatabaseColumnType.Nvarchar;
                    else
                        databaseColumnDataType = DatabaseColumnType.Varchar;
                    //尝试用低代码数据类型转换为Db识别的数据类型
                    if (DataTypeUtil.TryConvertToDatabaseColumnType(dataTypeStr, out databaseColumnDataType))
                    {
                        databaseColumnType = databaseColumnDataType;
                    }
                    isNullable = leftColumn.DataColumn.IsNullable;
                }
                if (leftColumn?.DataColumn != null && leftColumn.DataColumn.IsInternational)
                {
                    Dictionary<string, string> mapping = InternationalTools.GetLanguageMapping(InternationalMode.CurrentUser);
                    if (mapping.Count == 1)
                    {
                        var dataObject = _dataQuery.DataQuerySources.FirstOrDefault(s => s.ID == leftColumn.DataQuerySourceID).DataObject;
                        if (dataObject != null)
                        {
                            var realColumnName = InternationalTools.GetRealFieldName(dataObject, leftColumn.DataColumn.ColumnName, mapping.First().Key);
                            var prefix = parser.Filter.Code.Split('.')[0];
                            parser.Filter.Code = prefix + "." + realColumnName;
                        }
                    }
                }
            }

            DealWithSpecialOperator(filter);
            parser.ValueCode = "@" + paramCode;  //条件内部加入@前缀，跟Param中匹配起来
            string condSQL = parser.ToSQL();
            if (filter.Condition == QueryOperator.Between)
            {
                if (filter.Value.Count == 2)
                {
                    var code_prev = paramCode + "_Prev";
                    var code_next = paramCode + "_Next";
                    _params[code_prev] = new FilterParam { ParamCode = code_prev, ParamValue = filter.Value[0], DatabaseColumnType = databaseColumnType, DataType = dataType, IsNullabled = isNullable, Operator = filter.Condition };
                    _params[code_next] = new FilterParam { ParamCode = code_prev, ParamValue = filter.Value[1], DatabaseColumnType = databaseColumnType, DataType = dataType, IsNullabled = isNullable, Operator = filter.Condition };
                }
                else
                    throw new Exception(string.Format(CommonStringClassEngine.QueryPlanFilterHelper_ValueFilterToSQL, JsonConvert.SerializeObject(filter)));
            }
            else if (filter.Condition == QueryOperator.In || filter.Condition == QueryOperator.NotIn)
            {
                var flag = filter.Condition == QueryOperator.In;
                for (int i = 0; i < filter.Value.Count; i++)
                {
                    var key = paramCode + "_" + (flag ? "IN_" : "NOTIN_") + i;
                    _params[key] = new FilterParam { ParamCode = key, ParamValue = filter.Value[i], DatabaseColumnType = databaseColumnType, DataType = dataType, IsNullabled = isNullable, Operator = filter.Condition };
                }
            }
            else
            {
                if (filter.Value[0] != null)    //输入参数为null时生成 xxx is null语句，不需要变量替换
                    _params[paramCode] = new FilterParam { ParamCode = paramCode, ParamValue = filter.Value[0], DatabaseColumnType = databaseColumnType, DataType = dataType, IsNullabled = isNullable, Operator = filter.Condition };

            }
            //todo:直接生成IDbParameter集合，根据Filter.DataType填充Parameter.DbType字段。现在默认不传

            return condSQL;
        }

        /// <summary>
        /// 生成SQL参数Code
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        private string GenerateParameterCode(Filter filter, int groupDepth, int groupOffset, int filterOffset)
        {
            //外面加过@符号，此处不用再处理
            var code = $"Param_{groupDepth}_{groupOffset}_{filterOffset}_" + filter.Code.Replace(".", "_");
            if (_params.Keys.Contains(code))
                return code + "_" + Guid.NewGuid().ToString("N");
            return code;
        }

        /// <summary>
        /// 根据DB获取参数实体
        /// </summary>
        /// <returns></returns>
        private List<IDbDataParameter> DataPrameterTransfer()
        {
            if (_params.Keys.Count == 0)
            {
                return null;
            }
            List<IDbDataParameter> dbDataParameters = new List<IDbDataParameter>();
            foreach (var key in _params.Keys)
            {
                IDbDataParameter paramter = SampleDBFactory.GetParameter(_databaseType);
                paramter.ParameterName = key;
                paramter.Value = _params[key].ParamValue;

                dbDataParameters.Add(paramter);
            }
            return dbDataParameters;
        }

        /// <summary>
        /// 特殊处理IN NOTIN操作
        /// </summary>
        /// <param name="filter"></param>
        protected void DealWithSpecialOperator(Filter filter)
        {
            if (filter.Condition == QueryOperator.In || filter.Condition == QueryOperator.NotIn)
            {
                //当IN或NOT IN条件时，目前前端传入英文逗号分隔的选项，特殊处理
                if (filter.Value.Count == 1)
                {
                    List<string> input = filter.Value[0]?.Split(',').ToList();
                    if (input?.Count > 1)
                    {
                        filter.Value = input;
                    }
                }
            }
        }

        /// <summary>
        /// 判断查询条件是否使用了DQ自定义列。
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="dataQuery"></param>
        /// <param name="leftColumn">左值对应的列</param>
        /// <param name="rightColumn">右值对应的自定义列</param>
        /// <returns></returns>
        private bool IsUserDefinedColumnFilter(Filter filter, DataQuery dataQuery, out DataQueryColumn leftColumn, out DataQueryColumn rightColumn)
        {
            bool flag = false;
            leftColumn = rightColumn = null;
            var tmp = filter.Code.Split('.');
            if (!string.IsNullOrEmpty(filter.Code) && filter.Code.StartsWith(UserDefinedColumn))
            {
                string columnAlias = filter.Code.Substring(UserDefinedColumn.Length + 1);
                leftColumn = dataQuery.DataQueryColumns.FirstOrDefault(c => c.ColumnType == ColumnType.Express && c.Alias == columnAlias);
                flag = true;
            }
            if (filter.ValueType == AdvancedQueryValueType.Column)
            {
                string lastColumnName = filter.Value?.FirstOrDefault();
                if (!string.IsNullOrEmpty(lastColumnName) && lastColumnName.StartsWith(UserDefinedColumn))
                {
                    string lastColumnAlias = lastColumnName.Substring(UserDefinedColumn.Length + 1);
                    rightColumn = dataQuery.DataQueryColumns.FirstOrDefault(c => c.ColumnType == ColumnType.Express && c.Alias == lastColumnAlias);
                    flag = true;
                }
            }
            return flag;
        }

        /// <summary>
        /// 根据高级查询的FilterCode，找到在DQ中对应的列
        /// </summary>
        /// <param name="filterCode"></param>
        /// <param name="dataQuery"></param>
        /// <returns></returns>
        public static DataQueryColumn GetDataQueryColumnByFilterCode(string filterCode, DataQuery dataQuery)
        {
            DataQueryColumn dataQueryColumn = null;
            if (dataQuery == null || filterCode == null) return null;
            var arr = filterCode.Split('.');
            if (arr.Length != 2) return null;
            if (filterCode.StartsWith(UserDefinedColumn))
            {
                string columnAlias = filterCode.Substring(UserDefinedColumn.Length + 1);
                dataQueryColumn = dataQuery.DataQueryColumns.FirstOrDefault(c => c.ColumnType == ColumnType.Express && c.Alias == columnAlias);
            }
            else
            {
                var source = dataQuery.DataQuerySources.FirstOrDefault(s => s.Alias == arr[0]);
                if (source == null) return null;
                var sourceColumns = dataQuery.DataQueryColumns.Where(c => c.DataQuerySourceID == source.ID);
                dataQueryColumn = sourceColumns?.FirstOrDefault(c => c.Name == arr[1]);
                if (dataQueryColumn == null)
                    dataQueryColumn = sourceColumns?.FirstOrDefault(c => c.DataColumn != null && c.DataColumn.ColumnName == arr[1]);
            }
            return dataQueryColumn;
        }

        private void GetDataQueryColumnByFilter(Filter filter, DataQuery dataQuery, out DataQueryColumn leftColumn, out DataQueryColumn rightColumn)
        {
            rightColumn = leftColumn = null;
            if (dataQuery == null || filter == null) return;
            var arr = filter.Code.Split('.');
            if (arr != null && arr.Length != 2) return;
            leftColumn = GetDataQueryColumnByFilterCode(filter.Code, dataQuery);
            if (filter.ValueType == AdvancedQueryValueType.Column)
            {
                var rightColumnName = filter.Value?.FirstOrDefault();
                var rightArr = rightColumnName.Split('.');
                if (rightArr != null && rightArr.Length != 2) return;
                rightColumn = GetDataQueryColumnByFilterCode(rightColumnName, dataQuery);
            }
        }

        private FilterParser GetFilterParser(DatabaseType databaseType)
        {
            if (databaseType == DatabaseType.MySQL)
                return new MySQLFilterParser();
            else if (databaseType == DatabaseType.SQLServer)
                return new MSSQLFilterParser();
            else if (databaseType == DatabaseType.PostgreSQL)
                return new PostgreSQLFilterParser();
            else
                return default;
        }

        /// <summary>
        /// 强制类型转换
        /// </summary>
        /// <param name="paramCode"></param>
        /// <param name="inputVal"></param>
        /// <param name="dataType"></param>
        /// <param name="isNullabled"></param>
        /// <param name="isThrowException"></param>
        /// <returns></returns>
        /// <exception cref="FasException"></exception>
        private object Convert(string paramCode, object inputVal, DataType dataType, bool isNullabled, bool isThrowException = false)
        {
            var typer = DataTypeToolFactory.ValidAndConvertToObject(dataType);
            if (typer == null)
            {
                if (isThrowException)
                    throw new FasException(DataAccessErrorCode.ValidInput.NotSupportedType, string.Format(CommonStringClassEngine.SQLService_Convert, dataType));
            }
            else
            {
                try
                {
                    return typer.ValidAndConvertToObject(inputVal, isNullabled);
                }
                catch (Exception e)
                {
                    if (isThrowException)
                        throw new FasException(DataAccessErrorCode.ValidInput.ValidInputError, string.Format(CommonStringClassEngine.SQLService_Convert1, paramCode, dataType, inputVal, e.Message));
                }
            }
            return inputVal;
        }

        /// <summary>
        /// 对参数进行强制类型转换
        /// </summary>
        private void ValidateAndConvertParamListValue()
        {
            if (_params?.Count > 0)
            {
                foreach (FilterParam par in _params.Values)
                {
                    par.ParamValue = Convert(par.ParamCode, par.ParamValue, par.DataType, par.IsNullabled);
                    //当类型是字符串且为模糊匹配时，需要特殊处理参数
                    if (par.DataType == DataType.String && string.IsNullOrEmpty(par.Operator) && FilterParser.LikeOpe.Contains(par.Operator))
                    {
                        //todo:模糊匹配的运算符中，需要替换掉特殊字符 _ % [] \ 等等 
                    }
                }
            }
        }
        #endregion
    }

    public class FilterParam
    {
        public string ParamCode { get; set; }
        public object ParamValue { get; set; }
        public DatabaseColumnType DatabaseColumnType { get; set; } = DatabaseColumnType.Varchar;
        public DataType DataType { get; set; } = DataType.String;
        public bool IsNullabled { get; set; } = true;
        public string Operator {  get; set; }
    }
}